﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsRquisitionDataAccess
    {
        public static DataTable SelectMaterial(string MaterialID, string MaterialName, string IsRequirement, string RquisitionID, int pageIndex, int PageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select m.*,NULL AS MaterialRequirementDetailID,NULL AS MaterialRequirementID,
		                                        NULL AS PlanDetailID,NULL AS PieceNum,NULL AS [Weight],
		                                        NULL AS WorkOrderNo,NULL AS AdviseNum,NULL AS ResNum 
                                        from MD_Material m where 1=1 ";
                    if (!string.IsNullOrEmpty(IsRequirement) && IsRequirement == "1")
                    {
                        strcmd = @"select a.MaterialRequirementDetailID,
		                                    a.MaterialRequirementID,
                                            a.PlanDetailID,a.MaterialID,a.AdviseNum,a.PieceNum,a.[Weight],
                                            m.MaterialName,m.Specifications,m.Unit,m.Price,m.MMNumber,m.MeterWeight,
                                    isnull(a.AdviseNum,0)-isnull(RquisitionNum,0) as ResNum,ma.WorkOrderNo
                                    from BS_MaterialRequirementDetail a
                                    LEFT JOIN (SELECT aa.MaterialRequirementDetailID,SUM(aa.Nums) AS RquisitionNum
                                               FROM BS_RquisitionDetail aa
                                               WHERE 1=1 and aa.BillID<>@BillID
                                               GROUP BY aa.MaterialRequirementDetailID) b ON b.MaterialRequirementDetailID=a.MaterialRequirementDetailID
                                    left join MD_Material m on m.MaterialID=a.MaterialID
                                    left join BS_MaterialRequirement ma on ma.MaterialRequirementID=a.MaterialRequirementID
                                    where isnull(a.AdviseNum,0)-isnull(RquisitionNum,0)>0  ";
                        dpc.AddWithValue("@BillID", RquisitionID);
                    }
                    if (!string.IsNullOrEmpty(MaterialID))
                    {
                        strcmd += " and m.MaterialID like @MaterialID";
                        dpc.AddWithValue("@MaterialID", "%" + MaterialID + "%");
                    }
                    if (!string.IsNullOrEmpty(MaterialName))
                    {
                        strcmd += " and m.MaterialName like @MaterialName";
                        dpc.AddWithValue("@MaterialName", "%" + MaterialName + "%");
                    }
                    strcmd += " order by m.MaterialID desc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectList(string strBillID, string strFrom, string strTo, string strWorkOrderNo, string strMaterialName, int IRquisitionCount, out int icurPageIndex, int pageIndex, int pageSize, out int m_count)
        {

            try
            {
                #region SQL Server
                string strcmd = @"select A.* from V_Rquisition A WHERE 1=1 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and A.BillID LIKE @BillID";
                    dpc.AddWithValue("@BillID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and A.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strTo))
                {
                    strcmd += " and A.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and A.WorkOrderNo LIKE @WorkOrderNo";
                    dpc.AddWithValue("@WorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strMaterialName))
                {
                    strcmd += " and A.MaterialName LIKE @MaterialName";
                    dpc.AddWithValue("@MaterialName", "%" + strMaterialName + "%");
                }


                strcmd += " ORDER BY A.BillID DESC";

                return DataAccessCommon.ExecuteDataTablePagingExt(strcmd, out icurPageIndex, pageIndex, pageSize, dpc, IRquisitionCount, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }

        public static DataTable GetDataByBillID(string id)
        {

            try
            {
                #region SQL Server
                string strcmd = @"select r.BillDate,r.CreateDate,r.CreateUserID,r.CreateUserName,r.ModifyDate,r.ModifyUserID,r.ModifyUserName,
		                                rd.*,m.MaterialName,m.Specifications,m.Unit,m.MMNumber as SingleMMNumber,mr.AdviseNum,c.TotalRquNums,
		                                ISNULL(mr.AdviseNum,0)-ISNULL(c.TotalRquNums,0) as ResNum
                                from BS_Rquisition r
                                left join BS_RquisitionDetail rd on rd.BillID=r.BillID
                                left join MD_Material m on m.MaterialID=rd.MaterialID
                                left join BS_MaterialRequirementDetail mr on mr.MaterialRequirementDetailID=rd.MaterialRequirementDetailID
                                left join (select MaterialRequirementDetailID,SUM(ISNULL(Nums,0)) TotalRquNums --已请购总量
			                                from BS_RquisitionDetail
			                                where BillID <> @BillID
			                                group by MaterialRequirementDetailID) c on c.MaterialRequirementDetailID=rd.MaterialRequirementDetailID
                                where r.BillID = @BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                dpc.AddWithValue("@BillID", id);

                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
                #endregion
            }
            catch
            {
                throw;
            }
        }
    }
}
